﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using WD.Db.Contract;

namespace WD.Web.MVC.Common
{
    public class Tools
    {

        /// <summary>  
        /// 导出Excel  
        /// </summary>  
        /// <param name="lists">集合</param>  
        /// <param name="filePath">保存路径</param>
        /// <param name="title">XLS标题</param>
        /// <param name="sheetName">Sheet名称</param>  
        public void ExportOrderExcelFile<T>(List<T> lists, string filePath, string title = "", string sheetName = "")
        {
            try
            {
                var dir = filePath.Substring(0, filePath.LastIndexOf("\\", StringComparison.Ordinal));
                if (!Directory.Exists(dir)) Directory.CreateDirectory(dir);
                var workbook = new HSSFWorkbook();

                var type = typeof(T);
                var properties = type.GetProperties();

                var rowIndex = 0;   //当前行号
                var sheet = workbook.CreateSheet(string.IsNullOrWhiteSpace(sheetName) ? "Sheet1" : sheetName);
                if (!string.IsNullOrWhiteSpace(title))
                {
                    //列头
                    var titleRow = sheet.CreateRow(rowIndex);
                    titleRow.Height = 30 * 20; //行高设置数值是像素点的1/20
                    var cell = titleRow.CreateCell(0);
                    cell.SetCellValue(title);

                    ICellStyle style = workbook.CreateCellStyle();
                    //设置单元格的样式：水平对齐居中
                    style.Alignment = HorizontalAlignment.Center;
                    style.VerticalAlignment = VerticalAlignment.Center;

                    //新建一个字体样式对象
                    IFont font = workbook.CreateFont();
                    //设置字体加粗样式
                    font.Boldweight = short.MaxValue;
                    font.FontHeight = 15 * 20;
                    //使用SetFont方法将字体样式添加到单元格样式中 
                    style.SetFont(font);
                    //将新的样式赋给单元格
                    cell.CellStyle = style;

                    //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
                    //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1));
                    rowIndex++;
                }


                //列头
                var headerRow = sheet.CreateRow(rowIndex);
                headerRow.Height = 20 * 20; //行高设置数值是像素点的1/20

                var headRowStyle = workbook.CreateCellStyle();
                //设置单元格的样式：水平对齐居中
                headRowStyle.Alignment = HorizontalAlignment.Center;
                headRowStyle.VerticalAlignment = VerticalAlignment.Center;
                //新建一个字体样式对象
                var headRowFont = workbook.CreateFont();
                //设置字体加粗样式
                headRowFont.Boldweight = short.MaxValue;
                headRowStyle.SetFont(headRowFont);

                // headerRow.RowStyle = headRowStyle;
                rowIndex++;

                //是否为表头
                var h = false;
                foreach (var item in lists)
                {
                    var dataRow = sheet.CreateRow(rowIndex);
                    var i = 0;
                    foreach (var column in properties)
                    {
                        object val = "";
                        try
                        {

                            val = column.GetValue(item, null);
                        }
                        catch (Exception)
                        { }



                        if (!h)
                        {
                            var headCell = headerRow.CreateCell(i);
                            headCell.SetCellValue(column.Name);
                            headCell.CellStyle = headRowStyle;
                        }
                        dataRow.CreateCell(i).SetCellValue(val == null ? "" : val.ToString());
                        i++;
                    }
                    h = true;
                    rowIndex++;
                }

                //自适应列宽度
                for (var i = 0; i < headerRow.Count(); i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //将工作簿写入文件
                using (var fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    workbook.Write(fs);
                }

            }
            catch (Exception ee)
            {
                throw ee;
            }
        }



        /// <summary>  
        /// 导出Excel  
        /// </summary>  
        /// <param name="lists">集合</param>  
        /// <param name="filePath">保存路径</param>
        /// <param name="title">XLS标题</param>
        /// <param name="sheetName">Sheet名称</param>  
        public void ExportExcelFile<T>(List<T> lists, string filePath, string title = "", string sheetName = "")
        {
            try
            {
                var dir = filePath.Substring(0, filePath.LastIndexOf("\\", StringComparison.Ordinal));
                if (!Directory.Exists(dir)) Directory.CreateDirectory(dir);
                var workbook = new HSSFWorkbook();

                var type = typeof(T);
                var properties = type.GetProperties();

                var rowIndex = 0;   //当前行号
                var sheet = workbook.CreateSheet(string.IsNullOrWhiteSpace(sheetName) ? "Sheet1" : sheetName);
                if (!string.IsNullOrWhiteSpace(title))
                {
                    //列头
                    var titleRow = sheet.CreateRow(rowIndex);
                    titleRow.Height = 30 * 20; //行高设置数值是像素点的1/20
                    var cell = titleRow.CreateCell(0);
                    cell.SetCellValue(title);

                    ICellStyle style = workbook.CreateCellStyle();
                    //设置单元格的样式：水平对齐居中
                    style.Alignment = HorizontalAlignment.Center;
                    style.VerticalAlignment = VerticalAlignment.Center;

                    //新建一个字体样式对象
                    IFont font = workbook.CreateFont();
                    //设置字体加粗样式
                    font.Boldweight = short.MaxValue;
                    font.FontHeight = 15 * 20;
                    //使用SetFont方法将字体样式添加到单元格样式中 
                    style.SetFont(font);
                    //将新的样式赋给单元格
                    cell.CellStyle = style;

                    //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
                    //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1));
                    rowIndex++;
                }


                //列头
                var headerRow = sheet.CreateRow(rowIndex);
                headerRow.Height = 20 * 20; //行高设置数值是像素点的1/20

                var headRowStyle = workbook.CreateCellStyle();
                //设置单元格的样式：水平对齐居中
                headRowStyle.Alignment = HorizontalAlignment.Center;
                headRowStyle.VerticalAlignment = VerticalAlignment.Center;
                //新建一个字体样式对象
                var headRowFont = workbook.CreateFont();
                //设置字体加粗样式
                headRowFont.Boldweight = short.MaxValue;
                headRowStyle.SetFont(headRowFont);

                // headerRow.RowStyle = headRowStyle;
                rowIndex++;

                //是否为表头
                var h = false;
                foreach (var item in lists)
                {
                    var dataRow = sheet.CreateRow(rowIndex);
                    var i = 0;
                    foreach (var column in properties)
                    {
                        object val = "";
                        try
                        {

                            val = column.GetValue(item, null);
                        }
                        catch (Exception)
                        { }



                        if (!h)
                        {
                            var headCell = headerRow.CreateCell(i);
                            headCell.SetCellValue(column.Name);
                            headCell.CellStyle = headRowStyle;
                        }
                        dataRow.CreateCell(i).SetCellValue(val == null ? "" : val.ToString());
                        i++;
                    }
                    h = true;
                    rowIndex++;
                }

                //自适应列宽度
                for (var i = 0; i < headerRow.Count(); i++)
                {
                    sheet.AutoSizeColumn(i);
                }

                //将工作簿写入文件
                using (var fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    workbook.Write(fs);
                }

            }
            catch (Exception ee)
            {
                throw ee;
            }
        }

        /// <summary>
        /// 获取省份列表
        /// </summary>
        /// <param name="proStr">不用加载的省份（多省份用,分割）</param>
        /// <returns></returns>
        public List<ZtreeNode> GetPro(string proStr = "")
        {
            var tmpList = proStr.Split(',');
            var nodes = new List<ZtreeNode>();
            var i = 0;
            foreach (var item in StaticData.ProList.Where(item => !tmpList.Contains(item)))
            {
                i++;
                var node = new ZtreeNode
                {
                    id = i,
                    name = item,
                    pId = 0
                };
                nodes.Add(node);
            }
            return nodes;
        }
    }
}